#define TRACE
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;

using System.Diagnostics;

/// <summary>
/// Summary description for DataGateway
/// </summary>
public class DataGateway
{
    private string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    private OracleConnection connection;
    private OracleCommand command;
    private OracleDataAdapter adapter;
    ConnectionState constate;
    //

    private string strMsg="";

    public DataGateway()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public DataSet GetReportDS(string sql,string strTable)
    {

        ConnectDB();

        command.CommandText = sql;

        OracleDataAdapter adapter = new OracleDataAdapter(command);
        DataSet ds = new DataSet();
        try
        {
            
            adapter.Fill(ds, strTable.ToUpper());
        }
        catch
        {
            return null;
        }
        finally
        {
            CloseDB();
        }
        return ds;

    }

    //select  agent_id || ' / ' || name as ctl_text,agent_id as ctl_value from v_agent 


    private DataSet getSQLResult(string sql)
    {
        command.Parameters.Clear();
        command.CommandType = CommandType.Text;
        command.CommandText = sql;
        DataSet ds = new DataSet();
        try
        {
            adapter.Fill(ds);
        }
        catch (Exception error)
        {
            Trace.WriteLine(String.Format("{0} {1:s} / {2}", DateTime.Now, error.Message, command.CommandText));
            strMsg = error.Message;
        }

        return ds;
    }

    private bool ConnectDB()
    {
        //
        // TODO: Add constructor logic here
        //
        connection = new OracleConnection(connectionString);
        command = connection.CreateCommand();
        adapter = new OracleDataAdapter(command);

        strMsg = "";

        try
        {
            connection.Open();
            constate = connection.State;

            constate = ConnectionState.Closed;
        }
        catch (Exception error)
        {
            Trace.WriteLine(String.Format("{0} {1:s}", DateTime.Now, error.Message));
            strMsg = error.Message;
            return false;
        }
        return true;
    }


    public void CloseDB()
    {
        adapter.Dispose();
        command.Dispose();
        connection.Close();  // explicit close connection with db
        connection.Dispose();
    }

    public DataSet getCategoryGroup(DataSet dsCategory)
    {
        // Get a clone of the original DataSet.
        DataSet NewSet = dsCategory.Clone();

        DataSet copyDataSet;
        copyDataSet = dsCategory.Copy();
        DataView dsub = copyDataSet.Tables[0].DefaultView;
        DataRowView rowitem = null;


        DataView dv = dsCategory.Tables[0].DefaultView;
        dv.RowFilter = " parent_id is null ";

        DataRow row=null;

        row = NewSet.Tables[0].NewRow();
        row["ctl_text"] = "All Categories";
        row["ctl_value"] = DBNull.Value;
        row["parent_id"] = DBNull.Value;
        NewSet.Tables[0].Rows.Add(row);

        for (int i = 0; i < dv.Count; i++)
        {

            rowitem = dv[i];
            dsub.RowFilter = String.Format(" parent_id = {0:s} ", Convert.ToString( dv[i]["ct_id"] ));
   
            //
            row = NewSet.Tables[0].NewRow();
            row["ctl_text"] = rowitem["ctl_text"];
            row["ctl_value"] = rowitem["ctl_value"];
            row["parent_id"] = rowitem["parent_id"];
            NewSet.Tables[0].Rows.Add(row);
            //

            for (int j = 0; j < dsub.Count; j++)
            {
                rowitem = dsub[j];

                //
                row = NewSet.Tables[0].NewRow();
                row["ctl_text"] = "--" + rowitem["ctl_text"];
                row["ctl_value"] = rowitem["ctl_value"];
                row["parent_id"] = rowitem["parent_id"];
                NewSet.Tables[0].Rows.Add(row);
                //
            }
        }

        return NewSet;

    }

    public DataSet GetBranch()
    {
        ConnectDB();
        string strSQL = "";


        strSQL = "select name as ctl_text , pd_id as ctl_value from v_production";

        DataSet ds = getSQLResult(strSQL);


        return ds;
    }

    public string GetUserSite(int uid)
    {
        ConnectDB();
        string strSQL="";


        strSQL = string.Format("select * from v_user where u_id={0:d}", uid);

        DataSet ds = getSQLResult(strSQL);

        if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0 )
            return ds.Tables[0].Rows[0]["site"].ToString();
        else
            return "";
    }


    public DataSet GetPageLink(int pl_id, string strPubDate)
    {
        string strSQL = "";
        strSQL = string.Format(" select * from vf_advcompose_preview t where ai_date =  to_date('{0}','MM/DD/YYYY') and ai_pubid = {1}", strPubDate, pl_id);

        if (ConnectDB() == false)
            return null;

        DataSet ds = getSQLResult(strSQL);
        return ds;
    }


}

